package com.example.yls.android_sql;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;
import android.widget.Toast;

import java.io.File;

public class MainActivity extends AppCompatActivity {
    private Button btnCreateDB;
    private Button btnCreateTable;
    private Button btnInsert;
    private SQLiteDatabase db;
    private EditText edtName;
    private EditText edtNo;
    private EditText edtAge;
    private Button btnRead;
    private TextView txtData;
    private Button deletesql;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        btnCreateDB = findViewById(R.id.btn_createSql);
        btnCreateTable = findViewById(R.id.btn_createStudent);
        btnInsert = findViewById(R.id.btn_insertSql);
        edtName = findViewById(R.id.edt_Name);
        edtAge = findViewById(R.id.edt_Age);
        edtNo = findViewById(R.id.edt_No);
        btnRead = findViewById(R.id.btn_readSql);
        txtData = findViewById(R.id.btn_txtSql);
        deletesql = findViewById(R.id.btn_deleteSql);


        btnCreateDB.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                createOrOpenDB();
            }
        });


        btnInsert.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {


                String name = edtName.getText().toString();
                String no = edtNo.getText().toString();
                int age = Integer.valueOf(edtAge.getText().toString());
                btnInsert(no, name, age);


            }
        });

        btnRead.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                readDataFromDbB();
            }
        });
        btnCreateTable.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                btnCreateTable();
            }
        });

        deletesql.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                deleteSQL();
            }
        });
    }

    private void deleteSQL() {
        Toast.makeText(MainActivity.this,
                "你已成功删除数据库", Toast.LENGTH_SHORT).show();
            db = null;

    }

    /*读取学生数据并显示*/
    private void readDataFromDbB() {
        if (db == null) {
            createOrOpenDB();
        }

        Cursor cursor = db.query("student", null, null,
                null, null, null, null);

        StringBuffer buffer = new StringBuffer();
        for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()) {
            String no = cursor.getString(0);
            String name = cursor.getString(1);
            int age = cursor.getInt(2);

            buffer.append(no).append("  ").append(name).append("  ").append(age).append("\n");
        }

        txtData.setText(buffer);
    }

    /*插入学生数据*/
    private void btnInsert(String no, String name, int age) {
        if (db == null) {
            createOrOpenDB();
        }
        /* 第一种*/
   /* String sql = "insert into student values ('" + no + "','" + name + "'," + age + ")";
          db.execSQL(sql);*/

/*第二种*/
        ContentValues values = new ContentValues();
        values.put("stuno", no);
        values.put("name", name);
        values.put("age", age);
        db.insert("student", null, values);
        Toast.makeText(MainActivity.this,
                "你已成功插入学生数据", Toast.LENGTH_SHORT).show();

    }
    /*创建学生表*/
    private void btnCreateTable() {
        if (db == null) {
            createOrOpenDB();
        }
        String sql = "create table if not exists student" +
                " (stuno varchar(20),name varchar(20)," + " age int)";
        db.execSQL(sql);
        Toast.makeText(MainActivity.this,
                "你已成功创建学生表", Toast.LENGTH_SHORT).show();
    }

    /*创建学生数据库*/
    private void createOrOpenDB() {
        String path = getFilesDir().getAbsolutePath()
                + File.separator + "stu.db";

        db = SQLiteDatabase.openOrCreateDatabase(path, null);
        Toast.makeText(MainActivity.this,
                "你已成功创建数据库", Toast.LENGTH_SHORT).show();
    }
}
